package com.oceansoft.core.common.util;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.UUID;

/**
 * @author wanglin
 * 2012-3-27
 * 
 */
public class GzkExcelUtil {
	static final ThreadLocal<GzkExcelUtil> excelOperator = new ThreadLocal<GzkExcelUtil>();

	/**
	 * 创建excel返回excel的路径名
	 * @param request
	 * @param sheetName
	 * @param fileName
	 * @param lst
	 * @param starttime2
	 * @param endtime2
	 * @return
	 */
	public static GzkExcelUtil getInstance() {
		GzkExcelUtil eop = excelOperator.get();
		if (eop == null) {
			eop = new GzkExcelUtil();
			excelOperator.set(eop);
		}
		return eop;
	}

	public void removeInstance() {

		try {
			GzkExcelUtil eop = excelOperator.get();
			if (eop != null) {
				excelOperator.remove();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public String createExcel(HttpServletRequest request, String sheetName,
			List<HashMap<String, Object>> lst,
			HashMap<String, String> titleMap, List<String> lableList,
			HashMap<String, HashMap<String, String>> replaceMap) {

		try {
			String path = (request.getSession().getServletContext()
					.getRealPath("/"))
					+ "/"
					+ UUID.randomUUID().toString() + ".xls";
			WritableWorkbook book = null;
			book = Workbook.createWorkbook(new File(path));
			WritableSheet sheet = book.createSheet(sheetName, 0);
			sheet.getSettings().setDefaultColumnWidth(15);
			sheet.getSettings().setDefaultRowHeight(400);
			WritableFont font = new WritableFont(WritableFont.ARIAL, 14,
					WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat cf = new WritableCellFormat(font);
			int titlecount = 0;
			for (String s : lableList) {
				Label label0 = new Label(titlecount, 0, titleMap.get(s), cf);
				sheet.addCell(label0);
				titlecount++;
			}
			for (int i = 0; i < lst.size() && lst.size() > 0; i++) {
				HashMap<String, Object> map = lst.get(i);
				int count = 0;
				for (String key : lableList) {
					String value = map.get(key) == null ? "" : map.get(key)
							.toString();
				//
					Label _label = new Label(count, i + 1, value);
					sheet.addCell(_label);
					count++;
				}
			}

			book.write();
			book.close();
			return path;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	/**
	 * 文件下载
	 * 
	 * @param response
	 * @param fileName
	 * @param file
	 */
	public void downloadFile(HttpServletResponse response, String fileName,
			File file) {
		response.reset();
		InputStream is = null;
		OutputStream os = null;
		try {
			response.setContentType("application/octet-stream");
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String((fileName).getBytes("GBK"), "ISO-8859-1"));
			is = new FileInputStream(file);
			os = new BufferedOutputStream(response.getOutputStream());
			byte[] bytes = new byte[4096];
			int len;
			while ((len = is.read(bytes)) != -1) {
				os.write(bytes, 0, len);
			}
		} catch (Exception ex) {

		} finally {
			try {
				os.flush();
				os.close();
				is.close();
			} catch (IOException e) {

			}
		}
	}	
	/**导入 excel 时可以将excel的行转换成对应的实体
	 * @param <T>
	 * @param filePath excel文件路径
	 * @param array  excel每列的列头名称
	 * @param clz  将对应的行转成 对应的  实体
	 * @return
	 * @throws Exception
	 */
	public  <T> List<T> parseExcelToList(String filePath, String[] array,
			Class<T> clz) throws Exception {

		List<T> list = new ArrayList<T>();
		File file = new File(filePath);
		Workbook wb = null;
		try {
			// 传入的是文件 ，获取工作表
			wb=Workbook.getWorkbook(file);
			Sheet[] sheets=wb.getSheets();
			if(null!=sheets&&sheets.length>0){
				for(int i=0,size=sheets.length;i<size;i++){
					Sheet sheet=sheets[i];
					//行数
					int rows=sheet.getRows();
					for(int row=1;row<rows && rows > 0;row++){
						//列
						Cell[] cells=sheet.getRow(row);
						if(null!=cells&&cells.length>0){
							//使用反射方式创建泛型对象
							T obj=clz.newInstance();
							Method[] methods=clz.getMethods();
							for(int col=0;col<cells.length;col++){
								//单元格
								Cell cell=cells[col];
								//单元格内容
								String content=cell.getContents();
								//使用反射方式把单元格中的内容复制到对象对应的属性上
								if(!"".equals(content)&&null!=content){
									
									String methodname="";
									try {
										methodname = "set"+ array[col].substring(0, 1).toUpperCase()+ array[col].substring(1);
									} catch (Exception e) {
										System.out.println(content+"......."+array[col]+"   ppppp"+col);
										e.printStackTrace();
									}
									for (int mm = 0; mm < methods.length && methods.length > 0; mm++) {
										Method method = methods[mm];
										if (methodname.equals(method.getName())) {
											content=content.trim();
											method.invoke(obj, content);
											continue;
										}
									}
								}
							}
							//把对象添加到list
							list.add(obj);
						}
					}
				}
			}
		} catch (Exception e) {
			throw e;
		}finally
		{
			if(null!=wb)
			{
				wb.close();
			}
		}
		return list;
	}
	public  <T> List<T> parseExcelToListTemp(String filePath, String[] array,
			Class<T> clz) throws Exception {

		List<T> list = new ArrayList<T>();
		File file = new File(filePath);
		Workbook wb = null;
		try {
			// 传入的是文件 ，获取工作表
			wb=Workbook.getWorkbook(file);
			Sheet[] sheets=wb.getSheets();
			if(null!=sheets&&sheets.length>0){
				for(int i=0,size=sheets.length;i<size;i++){
					Sheet sheet=sheets[i];
					//行数
					int rows=sheet.getRows();
					for(int row=1;row<rows && rows > 0;row++){
						//列
						Cell[] cells=sheet.getRow(row);
						if(null!=cells&&cells.length>0){
							//使用反射方式创建泛型对象
							T obj=clz.newInstance();
							
							Cell cell0=cells[0];
							String content=cell0.getContents();
							if(!"".equals(content)&&null!=content){
								content=content.trim();
								Method method0 =clz.getMethod("setGzryxm", String.class);
								method0.invoke(obj, content);
							}
							Cell cell1=cells[1];
							String content1=cell1.getContents();
							if(!"".equals(content1)&&null!=content1){
								content1=content1.trim();
								Method method1 =clz.getMethod("setGzrysfzh", String.class);
								method1.invoke(obj, content1);
							}
							String gzly="";
							Cell cell2=cells[2];
							String content2=cell2.getContents();
							if(!"".equals(content2)&&null!=content2){
								content2=content2.trim();
								gzly+=content2;
							}
							Cell cell3=cells[3];
							String content3=cell3.getContents();
							if(!"".equals(content3)&&null!=content3){
								content3=content3.trim();
								gzly+=content3;
							}
							Cell cell4=cells[4];
							String content4=cell4.getContents();
							if(!"".equals(content4)&&null!=content4){
								content4=content4.trim();
								gzly+=content4;
							}
							Cell cell5=cells[5];
							String content5=cell5.getContents();
							if(!"".equals(content5)&&null!=content5){
								content5=content5.trim();
								gzly+=content5;
							}
							Cell cell6=cells[6];
							String content6=cell6.getContents();
							if(!"".equals(content6)&&null!=content6){
								content6=content6.trim();
								gzly+=content6;
							}
							Cell cell7=cells[7];
							String content7=cell7.getContents();
							if(!"".equals(content7)&&null!=content7){
								content7=content7.trim();
								gzly+=content7;
							}
							if(!"".equals(gzly)&&null!=gzly){
								Method method1 =clz.getMethod("setGzly", String.class);
								method1.invoke(obj, gzly);
							}
							//把对象添加到list
							list.add(obj);
						}
					}
				}
			}
		} catch (Exception e) {
			throw e;
		}finally
		{
			if(null!=wb)
			{
				wb.close();
			}
		}
		return list;
	}
}